{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from tabulate import tabulate\n",
    "from datetime import datetime\n",
    "import holidays\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('C:/Users/anbuv/Downloads/archive/Marketing.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>c_date</th>\n",
       "      <th>campaign_name</th>\n",
       "      <th>category</th>\n",
       "      <th>campaign_id</th>\n",
       "      <th>impressions</th>\n",
       "      <th>mark_spent</th>\n",
       "      <th>clicks</th>\n",
       "      <th>leads</th>\n",
       "      <th>orders</th>\n",
       "      <th>revenue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2021-02-01</td>\n",
       "      <td>facebook_tier1</td>\n",
       "      <td>social</td>\n",
       "      <td>349043</td>\n",
       "      <td>148263</td>\n",
       "      <td>7307.37</td>\n",
       "      <td>1210</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>4981.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2021-02-01</td>\n",
       "      <td>facebOOK_tier2</td>\n",
       "      <td>social</td>\n",
       "      <td>348934</td>\n",
       "      <td>220688</td>\n",
       "      <td>16300.20</td>\n",
       "      <td>1640</td>\n",
       "      <td>48</td>\n",
       "      <td>3</td>\n",
       "      <td>14962.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2021-02-01</td>\n",
       "      <td>google_hot</td>\n",
       "      <td>search</td>\n",
       "      <td>89459845</td>\n",
       "      <td>22850</td>\n",
       "      <td>5221.60</td>\n",
       "      <td>457</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>7981.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>2021-02-01</td>\n",
       "      <td>google_wide</td>\n",
       "      <td>search</td>\n",
       "      <td>127823</td>\n",
       "      <td>147038</td>\n",
       "      <td>6037.00</td>\n",
       "      <td>1196</td>\n",
       "      <td>24</td>\n",
       "      <td>1</td>\n",
       "      <td>2114.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>2021-02-01</td>\n",
       "      <td>youtube_blogger</td>\n",
       "      <td>influencer</td>\n",
       "      <td>10934</td>\n",
       "      <td>225800</td>\n",
       "      <td>29962.20</td>\n",
       "      <td>2258</td>\n",
       "      <td>49</td>\n",
       "      <td>10</td>\n",
       "      <td>84490.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id      c_date    campaign_name    category  campaign_id  impressions  \\\n",
       "0   1  2021-02-01   facebook_tier1      social       349043       148263   \n",
       "1   2  2021-02-01   facebOOK_tier2      social       348934       220688   \n",
       "2   3  2021-02-01       google_hot      search     89459845        22850   \n",
       "3   4  2021-02-01      google_wide      search       127823       147038   \n",
       "4   5  2021-02-01  youtube_blogger  influencer        10934       225800   \n",
       "\n",
       "   mark_spent  clicks  leads  orders  revenue  \n",
       "0     7307.37    1210     13       1   4981.0  \n",
       "1    16300.20    1640     48       3  14962.0  \n",
       "2     5221.60     457      9       1   7981.0  \n",
       "3     6037.00    1196     24       1   2114.0  \n",
       "4    29962.20    2258     49      10  84490.0  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 308 entries, 0 to 307\n",
      "Data columns (total 11 columns):\n",
      " #   Column         Non-Null Count  Dtype  \n",
      "---  ------         --------------  -----  \n",
      " 0   id             308 non-null    int64  \n",
      " 1   c_date         308 non-null    object \n",
      " 2   campaign_name  308 non-null    object \n",
      " 3   category       308 non-null    object \n",
      " 4   campaign_id    308 non-null    int64  \n",
      " 5   impressions    308 non-null    int64  \n",
      " 6   mark_spent     308 non-null    float64\n",
      " 7   clicks         308 non-null    int64  \n",
      " 8   leads          308 non-null    int64  \n",
      " 9   orders         308 non-null    int64  \n",
      " 10  revenue        308 non-null    float64\n",
      "dtypes: float64(2), int64(6), object(3)\n",
      "memory usage: 26.6+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data Cleaning\n",
    "\n",
    "### Changing the campaign date to datetime type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['c_date'] = pd.to_datetime(df['c_date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 308 entries, 0 to 307\n",
      "Data columns (total 11 columns):\n",
      " #   Column         Non-Null Count  Dtype         \n",
      "---  ------         --------------  -----         \n",
      " 0   id             308 non-null    int64         \n",
      " 1   c_date         308 non-null    datetime64[ns]\n",
      " 2   campaign_name  308 non-null    object        \n",
      " 3   category       308 non-null    object        \n",
      " 4   campaign_id    308 non-null    int64         \n",
      " 5   impressions    308 non-null    int64         \n",
      " 6   mark_spent     308 non-null    float64       \n",
      " 7   clicks         308 non-null    int64         \n",
      " 8   leads          308 non-null    int64         \n",
      " 9   orders         308 non-null    int64         \n",
      " 10  revenue        308 non-null    float64       \n",
      "dtypes: datetime64[ns](1), float64(2), int64(6), object(2)\n",
      "memory usage: 26.6+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The start date is: 2021-02-01 00:00:00\n"
     ]
    }
   ],
   "source": [
    "start_date = df['c_date'].min()\n",
    "print('The start date is:', start_date)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The end date is: 2021-02-28 00:00:00\n"
     ]
    }
   ],
   "source": [
    "end_date = df['c_date'].max()\n",
    "print('The end date is:', end_date)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As we can see the data we have is for a marketing campaign data for the month of February, 2021 for a company X."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Campaign Name cleaning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unique campaign names:\n",
      "['facebook_tier1' 'facebOOK_tier2' 'google_hot' 'google_wide'\n",
      " 'youtube_blogger' 'instagram_tier1' 'instagram_tier2'\n",
      " 'facebook_retargeting' 'facebook_lal' 'instagram_blogger'\n",
      " 'banner_partner']\n"
     ]
    }
   ],
   "source": [
    "print(\"Unique campaign names:\")\n",
    "print(df['campaign_name'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Fix the campaign name with incorrect capitalization\n",
    "df['campaign_name'] = df['campaign_name'].replace('facebOOK_tier2', 'facebook_tier2')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unique campaign names after correction:\n",
      "['facebook_tier1' 'facebook_tier2' 'google_hot' 'google_wide'\n",
      " 'youtube_blogger' 'instagram_tier1' 'instagram_tier2'\n",
      " 'facebook_retargeting' 'facebook_lal' 'instagram_blogger'\n",
      " 'banner_partner']\n"
     ]
    }
   ],
   "source": [
    "print(\"Unique campaign names after correction:\")\n",
    "print(df['campaign_name'].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data Engineering"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are 11 campaigns in the period on 5 platforms. We will create a new column platform_name which we will use for analysis later."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unique platforms after grouping:\n",
      "['facebook' 'google' 'youtube' 'instagram' 'banner']\n"
     ]
    }
   ],
   "source": [
    "# Create a function to extract platform name from campaign_name\n",
    "def get_platform_name(campaign):\n",
    "    campaign = campaign.lower()\n",
    "    if 'facebook' in campaign:\n",
    "        return 'facebook'\n",
    "    elif 'google' in campaign:\n",
    "        return 'google' \n",
    "    elif 'youtube' in campaign:\n",
    "        return 'youtube'\n",
    "    elif 'instagram' in campaign:\n",
    "        return 'instagram'\n",
    "    elif 'banner' in campaign:\n",
    "        return 'banner'\n",
    "    else:\n",
    "        return campaign\n",
    "\n",
    "# Apply the function to create new platform_name column\n",
    "df['platform_name'] = df['campaign_name'].apply(get_platform_name)\n",
    "\n",
    "# Display unique platforms to verify\n",
    "print(\"Unique platforms after grouping:\")\n",
    "print(df['platform_name'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>c_date</th>\n",
       "      <th>campaign_name</th>\n",
       "      <th>platform_name</th>\n",
       "      <th>category</th>\n",
       "      <th>campaign_id</th>\n",
       "      <th>impressions</th>\n",
       "      <th>mark_spent</th>\n",
       "      <th>clicks</th>\n",
       "      <th>leads</th>\n",
       "      <th>orders</th>\n",
       "      <th>revenue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2021-02-01</td>\n",
       "      <td>facebook_tier1</td>\n",
       "      <td>facebook</td>\n",
       "      <td>social</td>\n",
       "      <td>349043</td>\n",
       "      <td>148263</td>\n",
       "      <td>7307.37</td>\n",
       "      <td>1210</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>4981.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2021-02-01</td>\n",
       "      <td>facebook_tier2</td>\n",
       "      <td>facebook</td>\n",
       "      <td>social</td>\n",
       "      <td>348934</td>\n",
       "      <td>220688</td>\n",
       "      <td>16300.20</td>\n",
       "      <td>1640</td>\n",
       "      <td>48</td>\n",
       "      <td>3</td>\n",
       "      <td>14962.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2021-02-01</td>\n",
       "      <td>google_hot</td>\n",
       "      <td>google</td>\n",
       "      <td>search</td>\n",
       "      <td>89459845</td>\n",
       "      <td>22850</td>\n",
       "      <td>5221.60</td>\n",
       "      <td>457</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>7981.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>2021-02-01</td>\n",
       "      <td>google_wide</td>\n",
       "      <td>google</td>\n",
       "      <td>search</td>\n",
       "      <td>127823</td>\n",
       "      <td>147038</td>\n",
       "      <td>6037.00</td>\n",
       "      <td>1196</td>\n",
       "      <td>24</td>\n",
       "      <td>1</td>\n",
       "      <td>2114.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>2021-02-01</td>\n",
       "      <td>youtube_blogger</td>\n",
       "      <td>youtube</td>\n",
       "      <td>influencer</td>\n",
       "      <td>10934</td>\n",
       "      <td>225800</td>\n",
       "      <td>29962.20</td>\n",
       "      <td>2258</td>\n",
       "      <td>49</td>\n",
       "      <td>10</td>\n",
       "      <td>84490.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id     c_date    campaign_name platform_name    category  campaign_id  \\\n",
       "0   1 2021-02-01   facebook_tier1      facebook      social       349043   \n",
       "1   2 2021-02-01   facebook_tier2      facebook      social       348934   \n",
       "2   3 2021-02-01       google_hot        google      search     89459845   \n",
       "3   4 2021-02-01      google_wide        google      search       127823   \n",
       "4   5 2021-02-01  youtube_blogger       youtube  influencer        10934   \n",
       "\n",
       "   impressions  mark_spent  clicks  leads  orders  revenue  \n",
       "0       148263     7307.37    1210     13       1   4981.0  \n",
       "1       220688    16300.20    1640     48       3  14962.0  \n",
       "2        22850     5221.60     457      9       1   7981.0  \n",
       "3       147038     6037.00    1196     24       1   2114.0  \n",
       "4       225800    29962.20    2258     49      10  84490.0  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Reorder columns to put platform_name next to campaign_name\n",
    "cols = ['id', 'c_date', 'campaign_name', 'platform_name', 'category', 'campaign_id', 'impressions', 'mark_spent', 'clicks', 'leads', 'orders', 'revenue']\n",
    "df = df[cols]\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create day_of_week column using c_date\n",
    "df['day_of_week'] = pd.to_datetime(df['c_date']).dt.day_name()\n",
    "\n",
    "# Create is_holiday column\n",
    "us_holidays = holidays.US()\n",
    "df['is_holiday'] = df['c_date'].apply(lambda x: 'yes' if x in us_holidays else 'no')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>c_date</th>\n",
       "      <th>campaign_name</th>\n",
       "      <th>platform_name</th>\n",
       "      <th>category</th>\n",
       "      <th>campaign_id</th>\n",
       "      <th>impressions</th>\n",
       "      <th>mark_spent</th>\n",
       "      <th>clicks</th>\n",
       "      <th>leads</th>\n",
       "      <th>orders</th>\n",
       "      <th>revenue</th>\n",
       "      <th>day_of_week</th>\n",
       "      <th>is_holiday</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2021-02-01</td>\n",
       "      <td>facebook_tier1</td>\n",
       "      <td>facebook</td>\n",
       "      <td>social</td>\n",
       "      <td>349043</td>\n",
       "      <td>148263</td>\n",
       "      <td>7307.37</td>\n",
       "      <td>1210</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>4981.0</td>\n",
       "      <td>Monday</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2021-02-01</td>\n",
       "      <td>facebook_tier2</td>\n",
       "      <td>facebook</td>\n",
       "      <td>social</td>\n",
       "      <td>348934</td>\n",
       "      <td>220688</td>\n",
       "      <td>16300.20</td>\n",
       "      <td>1640</td>\n",
       "      <td>48</td>\n",
       "      <td>3</td>\n",
       "      <td>14962.0</td>\n",
       "      <td>Monday</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2021-02-01</td>\n",
       "      <td>google_hot</td>\n",
       "      <td>google</td>\n",
       "      <td>search</td>\n",
       "      <td>89459845</td>\n",
       "      <td>22850</td>\n",
       "      <td>5221.60</td>\n",
       "      <td>457</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>7981.0</td>\n",
       "      <td>Monday</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>2021-02-01</td>\n",
       "      <td>google_wide</td>\n",
       "      <td>google</td>\n",
       "      <td>search</td>\n",
       "      <td>127823</td>\n",
       "      <td>147038</td>\n",
       "      <td>6037.00</td>\n",
       "      <td>1196</td>\n",
       "      <td>24</td>\n",
       "      <td>1</td>\n",
       "      <td>2114.0</td>\n",
       "      <td>Monday</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>2021-02-01</td>\n",
       "      <td>youtube_blogger</td>\n",
       "      <td>youtube</td>\n",
       "      <td>influencer</td>\n",
       "      <td>10934</td>\n",
       "      <td>225800</td>\n",
       "      <td>29962.20</td>\n",
       "      <td>2258</td>\n",
       "      <td>49</td>\n",
       "      <td>10</td>\n",
       "      <td>84490.0</td>\n",
       "      <td>Monday</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id     c_date    campaign_name platform_name    category  campaign_id  \\\n",
       "0   1 2021-02-01   facebook_tier1      facebook      social       349043   \n",
       "1   2 2021-02-01   facebook_tier2      facebook      social       348934   \n",
       "2   3 2021-02-01       google_hot        google      search     89459845   \n",
       "3   4 2021-02-01      google_wide        google      search       127823   \n",
       "4   5 2021-02-01  youtube_blogger       youtube  influencer        10934   \n",
       "\n",
       "   impressions  mark_spent  clicks  leads  orders  revenue day_of_week  \\\n",
       "0       148263     7307.37    1210     13       1   4981.0      Monday   \n",
       "1       220688    16300.20    1640     48       3  14962.0      Monday   \n",
       "2        22850     5221.60     457      9       1   7981.0      Monday   \n",
       "3       147038     6037.00    1196     24       1   2114.0      Monday   \n",
       "4       225800    29962.20    2258     49      10  84490.0      Monday   \n",
       "\n",
       "  is_holiday  \n",
       "0         no  \n",
       "1         no  \n",
       "2         no  \n",
       "3         no  \n",
       "4         no  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exploratory Data Analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Basic statistics for numerical columns:\n",
      "               id               c_date   campaign_id   impressions  \\\n",
      "count  308.000000                  308  3.080000e+02  3.080000e+02   \n",
      "mean   154.500000  2021-02-14 12:00:00  9.605628e+06  5.122475e+06   \n",
      "min      1.000000  2021-02-01 00:00:00  1.093400e+04  6.670000e+02   \n",
      "25%     77.750000  2021-02-07 18:00:00  1.278230e+05  1.479568e+05   \n",
      "50%    154.500000  2021-02-14 12:00:00  3.747540e+05  6.028100e+05   \n",
      "75%    231.250000  2021-02-21 06:00:00  4.387490e+06  3.254450e+06   \n",
      "max    308.000000  2021-02-28 00:00:00  8.945984e+07  4.199700e+08   \n",
      "std     89.056162                  NaN  2.543040e+07  2.631134e+07   \n",
      "\n",
      "          mark_spent        clicks        leads      orders       revenue  \n",
      "count     308.000000    308.000000   308.000000  308.000000  3.080000e+02  \n",
      "mean    99321.038377   9739.996753   212.918831   26.113636  1.392512e+05  \n",
      "min       169.750000     20.000000     0.000000    0.000000  0.000000e+00  \n",
      "25%      9465.195000   1347.250000    20.750000    3.000000  8.580000e+03  \n",
      "50%     36765.150000   4349.500000    84.000000    9.000000  3.891700e+04  \n",
      "75%    129372.250000  13499.250000   270.500000   29.250000  1.370492e+05  \n",
      "max    880357.000000  61195.000000  1678.000000  369.000000  2.812520e+06  \n",
      "std    144008.011181  12157.476244   300.459978   42.741288  2.763031e+05  \n"
     ]
    }
   ],
   "source": [
    "# Basic statistics for numerical columns\n",
    "print(\"Basic statistics for numerical columns:\")\n",
    "print(df.describe())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Missing values in each column:\n",
      "id               0\n",
      "c_date           0\n",
      "campaign_name    0\n",
      "platform_name    0\n",
      "category         0\n",
      "campaign_id      0\n",
      "impressions      0\n",
      "mark_spent       0\n",
      "clicks           0\n",
      "leads            0\n",
      "orders           0\n",
      "revenue          0\n",
      "day_of_week      0\n",
      "is_holiday       0\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# Check for missing values\n",
    "print(\"Missing values in each column:\")\n",
    "print(df.isnull().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of zeros in each column:\n",
      "id                0\n",
      "c_date            0\n",
      "campaign_name     0\n",
      "platform_name     0\n",
      "category          0\n",
      "campaign_id       0\n",
      "impressions       0\n",
      "mark_spent        0\n",
      "clicks            0\n",
      "leads             4\n",
      "orders           19\n",
      "revenue          19\n",
      "day_of_week       0\n",
      "is_holiday        0\n",
      "dtype: int64\n",
      "\n",
      "Number of campaigns with:\n",
      "Zero leads: 4\n",
      "Zero orders: 19\n"
     ]
    }
   ],
   "source": [
    "# Count zeros in each column\n",
    "print(\"Number of zeros in each column:\")\n",
    "zero_counts = (df == 0).sum()\n",
    "print(zero_counts)\n",
    "\n",
    "# Count campaigns with zero leads or orders\n",
    "zero_leads = len(df[df['leads'] == 0])\n",
    "zero_orders = len(df[df['orders'] == 0])\n",
    "\n",
    "print(f\"\\nNumber of campaigns with:\")\n",
    "print(f\"Zero leads: {zero_leads}\")\n",
    "print(f\"Zero orders: {zero_orders}\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As we can see there are 19 records where we have zero orders and revenue. We also have 4 records with zero leads"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Campaigns with zero leads and orders:\n",
      "        c_date         campaign_name  leads  orders  revenue day_of_week  \\\n",
      "10  2021-02-01        banner_partner      3       0      0.0      Monday   \n",
      "12  2021-02-02        facebook_tier2     10       0      0.0     Tuesday   \n",
      "17  2021-02-02       instagram_tier2     14       0      0.0     Tuesday   \n",
      "18  2021-02-02  facebook_retargeting      0       0      0.0     Tuesday   \n",
      "82  2021-02-08       instagram_tier1      1       0      0.0      Monday   \n",
      "115 2021-02-11       instagram_tier1      1       0      0.0    Thursday   \n",
      "247 2021-02-23       instagram_tier1      1       0      0.0     Tuesday   \n",
      "266 2021-02-25            google_hot      1       0      0.0    Thursday   \n",
      "277 2021-02-26            google_hot      2       0      0.0      Friday   \n",
      "283 2021-02-26          facebook_lal      0       0      0.0      Friday   \n",
      "287 2021-02-27        facebook_tier2     13       0      0.0    Saturday   \n",
      "288 2021-02-27            google_hot      2       0      0.0    Saturday   \n",
      "289 2021-02-27           google_wide      5       0      0.0    Saturday   \n",
      "292 2021-02-27       instagram_tier2      3       0      0.0    Saturday   \n",
      "293 2021-02-27  facebook_retargeting      0       0      0.0    Saturday   \n",
      "294 2021-02-27          facebook_lal      2       0      0.0    Saturday   \n",
      "300 2021-02-28           google_wide      1       0      0.0      Sunday   \n",
      "303 2021-02-28       instagram_tier2      4       0      0.0      Sunday   \n",
      "304 2021-02-28  facebook_retargeting      0       0      0.0      Sunday   \n",
      "\n",
      "    is_holiday  \n",
      "10          no  \n",
      "12          no  \n",
      "17          no  \n",
      "18          no  \n",
      "82          no  \n",
      "115         no  \n",
      "247         no  \n",
      "266         no  \n",
      "277         no  \n",
      "283         no  \n",
      "287         no  \n",
      "288         no  \n",
      "289         no  \n",
      "292         no  \n",
      "293         no  \n",
      "294         no  \n",
      "300         no  \n",
      "303         no  \n",
      "304         no  \n"
     ]
    }
   ],
   "source": [
    "# Filter records where both leads and orders are zero\n",
    "zero_leads_orders = df[(df['leads'] == 0) | (df['orders'] == 0)]\n",
    "\n",
    "# Display relevant columns\n",
    "print(\"Campaigns with zero leads and orders:\")\n",
    "print(zero_leads_orders[['c_date', 'campaign_name', 'leads', 'orders', 'revenue', 'day_of_week', 'is_holiday']])\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As we can see from the above step, zero order days are spread out through out the week. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Marketing Metrics:\n",
    "* conversion_rate: Percentage of clicks that result in orders (orders/clicks * 100) ----- Shows how effectively we convert visitors into customers\n",
    "* cost_per_click (CPC): Marketing spend divided by number of clicks (mark_spent/clicks) ---- Shows how much we pay for each click/visitor\n",
    "* cost_per_lead (CPL): Marketing spend divided by number of leads (mark_spent/leads) ---- Shows how much we pay to acquire each lead\n",
    "* cost_per_order (CPO): Marketing spend divided by number of orders (mark_spent/orders) ---- Shows how much we pay to acquire each paying customer\n",
    "* return on ad spend (ROAS): Revenue divided by marketing spend (revenue/mark_spent) ---- Shows how much revenue we generate for each dollar spent on marketing\n",
    "* Click-through rate (CTR)\n",
    "* Return on Investment (ROI)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate key marketing metrics with handling for zero values\n",
    "df['conversion_rate'] = (df['orders'] / df['clicks']).replace([np.inf, -np.inf], 0) * 100\n",
    "df['cost_per_click'] = (df['mark_spent'] / df['clicks']).replace([np.inf, -np.inf], 0)\n",
    "df['cost_per_lead'] = (df['mark_spent'] / df['leads']).replace([np.inf, -np.inf], 0)\n",
    "df['cost_per_order'] = (df['mark_spent'] / df['orders']).replace([np.inf, -np.inf], 0)\n",
    "df['roas'] = (df['revenue'] / df['mark_spent']).replace([np.inf, -np.inf], 0)\n",
    "df['ctr'] = (df['clicks'] / df['impressions'] * 100).replace([np.inf,-np.inf],0)\n",
    "df['roi'] = (df['revenue'] - df['mark_spent']/df['mark_spent']*100).replace([np.inf,-np.inf],0)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c_date</th>\n",
       "      <td>2021-02-01 00:00:00</td>\n",
       "      <td>2021-02-01 00:00:00</td>\n",
       "      <td>2021-02-01 00:00:00</td>\n",
       "      <td>2021-02-01 00:00:00</td>\n",
       "      <td>2021-02-01 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>campaign_name</th>\n",
       "      <td>facebook_tier1</td>\n",
       "      <td>facebook_tier2</td>\n",
       "      <td>google_hot</td>\n",
       "      <td>google_wide</td>\n",
       "      <td>youtube_blogger</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>platform_name</th>\n",
       "      <td>facebook</td>\n",
       "      <td>facebook</td>\n",
       "      <td>google</td>\n",
       "      <td>google</td>\n",
       "      <td>youtube</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category</th>\n",
       "      <td>social</td>\n",
       "      <td>social</td>\n",
       "      <td>search</td>\n",
       "      <td>search</td>\n",
       "      <td>influencer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>campaign_id</th>\n",
       "      <td>349043</td>\n",
       "      <td>348934</td>\n",
       "      <td>89459845</td>\n",
       "      <td>127823</td>\n",
       "      <td>10934</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>impressions</th>\n",
       "      <td>148263</td>\n",
       "      <td>220688</td>\n",
       "      <td>22850</td>\n",
       "      <td>147038</td>\n",
       "      <td>225800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mark_spent</th>\n",
       "      <td>7307.37</td>\n",
       "      <td>16300.2</td>\n",
       "      <td>5221.6</td>\n",
       "      <td>6037.0</td>\n",
       "      <td>29962.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>clicks</th>\n",
       "      <td>1210</td>\n",
       "      <td>1640</td>\n",
       "      <td>457</td>\n",
       "      <td>1196</td>\n",
       "      <td>2258</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>leads</th>\n",
       "      <td>13</td>\n",
       "      <td>48</td>\n",
       "      <td>9</td>\n",
       "      <td>24</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>orders</th>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>revenue</th>\n",
       "      <td>4981.0</td>\n",
       "      <td>14962.0</td>\n",
       "      <td>7981.0</td>\n",
       "      <td>2114.0</td>\n",
       "      <td>84490.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>day_of_week</th>\n",
       "      <td>Monday</td>\n",
       "      <td>Monday</td>\n",
       "      <td>Monday</td>\n",
       "      <td>Monday</td>\n",
       "      <td>Monday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>is_holiday</th>\n",
       "      <td>no</td>\n",
       "      <td>no</td>\n",
       "      <td>no</td>\n",
       "      <td>no</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>conversion_rate</th>\n",
       "      <td>0.082645</td>\n",
       "      <td>0.182927</td>\n",
       "      <td>0.218818</td>\n",
       "      <td>0.083612</td>\n",
       "      <td>0.44287</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cost_per_click</th>\n",
       "      <td>6.039149</td>\n",
       "      <td>9.939146</td>\n",
       "      <td>11.425821</td>\n",
       "      <td>5.047659</td>\n",
       "      <td>13.269353</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cost_per_lead</th>\n",
       "      <td>562.105385</td>\n",
       "      <td>339.5875</td>\n",
       "      <td>580.177778</td>\n",
       "      <td>251.541667</td>\n",
       "      <td>611.473469</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cost_per_order</th>\n",
       "      <td>7307.37</td>\n",
       "      <td>5433.4</td>\n",
       "      <td>5221.6</td>\n",
       "      <td>6037.0</td>\n",
       "      <td>2996.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>roas</th>\n",
       "      <td>0.681641</td>\n",
       "      <td>0.917903</td>\n",
       "      <td>1.528459</td>\n",
       "      <td>0.350174</td>\n",
       "      <td>2.819886</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ctr</th>\n",
       "      <td>0.816117</td>\n",
       "      <td>0.743131</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.813395</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>roi</th>\n",
       "      <td>4881.0</td>\n",
       "      <td>14862.0</td>\n",
       "      <td>7881.0</td>\n",
       "      <td>2014.0</td>\n",
       "      <td>84390.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   0                    1  \\\n",
       "id                                 1                    2   \n",
       "c_date           2021-02-01 00:00:00  2021-02-01 00:00:00   \n",
       "campaign_name         facebook_tier1       facebook_tier2   \n",
       "platform_name               facebook             facebook   \n",
       "category                      social               social   \n",
       "campaign_id                   349043               348934   \n",
       "impressions                   148263               220688   \n",
       "mark_spent                   7307.37              16300.2   \n",
       "clicks                          1210                 1640   \n",
       "leads                             13                   48   \n",
       "orders                             1                    3   \n",
       "revenue                       4981.0              14962.0   \n",
       "day_of_week                   Monday               Monday   \n",
       "is_holiday                        no                   no   \n",
       "conversion_rate             0.082645             0.182927   \n",
       "cost_per_click              6.039149             9.939146   \n",
       "cost_per_lead             562.105385             339.5875   \n",
       "cost_per_order               7307.37               5433.4   \n",
       "roas                        0.681641             0.917903   \n",
       "ctr                         0.816117             0.743131   \n",
       "roi                           4881.0              14862.0   \n",
       "\n",
       "                                   2                    3                    4  \n",
       "id                                 3                    4                    5  \n",
       "c_date           2021-02-01 00:00:00  2021-02-01 00:00:00  2021-02-01 00:00:00  \n",
       "campaign_name             google_hot          google_wide      youtube_blogger  \n",
       "platform_name                 google               google              youtube  \n",
       "category                      search               search           influencer  \n",
       "campaign_id                 89459845               127823                10934  \n",
       "impressions                    22850               147038               225800  \n",
       "mark_spent                    5221.6               6037.0              29962.2  \n",
       "clicks                           457                 1196                 2258  \n",
       "leads                              9                   24                   49  \n",
       "orders                             1                    1                   10  \n",
       "revenue                       7981.0               2114.0              84490.0  \n",
       "day_of_week                   Monday               Monday               Monday  \n",
       "is_holiday                        no                   no                   no  \n",
       "conversion_rate             0.218818             0.083612              0.44287  \n",
       "cost_per_click             11.425821             5.047659            13.269353  \n",
       "cost_per_lead             580.177778           251.541667           611.473469  \n",
       "cost_per_order                5221.6               6037.0              2996.22  \n",
       "roas                        1.528459             0.350174             2.819886  \n",
       "ctr                              2.0             0.813395                  1.0  \n",
       "roi                           7881.0               2014.0              84390.0  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Metrics by Category:\n",
      "            conversion_rate  cost_per_click  cost_per_lead  cost_per_order  \\\n",
      "category                                                                     \n",
      "influencer             0.40           11.07         490.31         2755.58   \n",
      "media                  0.37           11.97         495.29         3209.88   \n",
      "search                 0.25           10.48         486.90         4179.23   \n",
      "social                 0.18            9.20         439.67         5236.62   \n",
      "\n",
      "            roas   ctr     roi  \n",
      "category                        \n",
      "influencer  2.54  0.96  154.29  \n",
      "media       1.22  0.04   22.41  \n",
      "search      1.07  0.46    7.07  \n",
      "social      0.86  0.42  -13.68  \n",
      "\n",
      "Metrics by Platform:\n",
      "               conversion_rate  cost_per_click  cost_per_lead  cost_per_order  \\\n",
      "platform_name                                                                   \n",
      "banner                    0.37           11.97         495.29         3209.88   \n",
      "facebook                  0.22           14.12         707.13         6500.68   \n",
      "google                    0.25           10.48         486.90         4179.23   \n",
      "instagram                 0.20            7.30         328.09         3629.11   \n",
      "youtube                   0.43            9.02         408.61         2120.13   \n",
      "\n",
      "               roas   ctr     roi  \n",
      "platform_name                      \n",
      "banner         1.22  0.04   22.41  \n",
      "facebook       0.66  0.49  -34.13  \n",
      "google         1.07  0.46    7.07  \n",
      "instagram      1.40  0.44   39.91  \n",
      "youtube        3.77  1.03  277.32  \n",
      "\n",
      "Metrics by Campaign:\n",
      "                      conversion_rate  cost_per_click  cost_per_lead  \\\n",
      "campaign_name                                                          \n",
      "banner_partner                   0.37           11.97         495.29   \n",
      "facebook_lal                     0.24           22.01        1383.94   \n",
      "facebook_retargeting             0.36            8.90         526.61   \n",
      "facebook_tier1                   0.20           10.69         725.54   \n",
      "facebook_tier2                   0.21           14.23         556.94   \n",
      "google_hot                       0.31           13.33         654.66   \n",
      "google_wide                      0.23            9.42         428.59   \n",
      "instagram_blogger                0.37           14.16         606.07   \n",
      "instagram_tier1                  0.28            9.50         386.80   \n",
      "instagram_tier2                  0.06            2.09         102.77   \n",
      "youtube_blogger                  0.43            9.02         408.61   \n",
      "\n",
      "                      cost_per_order  roas   ctr     roi  \n",
      "campaign_name                                             \n",
      "banner_partner               3209.88  1.22  0.04   22.41  \n",
      "facebook_lal                 8986.19  0.11  0.95  -88.64  \n",
      "facebook_retargeting         2467.28  2.01  3.07  101.50  \n",
      "facebook_tier1               5410.96  0.93  0.38   -6.57  \n",
      "facebook_tier2               6822.49  0.74  0.47  -26.22  \n",
      "google_hot                   4270.46  1.84  1.95   83.81  \n",
      "google_wide                  4132.36  0.66  0.35  -33.67  \n",
      "instagram_blogger            3861.24  1.37  0.88   36.75  \n",
      "instagram_tier1              3384.27  1.77  0.36   77.14  \n",
      "instagram_tier2              3406.24  0.63  0.37  -37.11  \n",
      "youtube_blogger              2120.13  3.77  1.03  277.32  \n"
     ]
    }
   ],
   "source": [
    "# Calculate metrics by category\n",
    "category_metrics = df.groupby('category').agg({\n",
    "    'orders': 'sum',\n",
    "    'clicks': 'sum',\n",
    "    'mark_spent': 'sum',\n",
    "    'leads': 'sum', \n",
    "    'revenue': 'sum',\n",
    "    'impressions': 'sum'\n",
    "}).assign(\n",
    "    conversion_rate=lambda x: round((x['orders'] / x['clicks'] * 100).replace([np.inf, -np.inf], 0), 2),\n",
    "    cost_per_click=lambda x: round((x['mark_spent'] / x['clicks']).replace([np.inf, -np.inf], 0), 2),\n",
    "    cost_per_lead=lambda x: round((x['mark_spent'] / x['leads']).replace([np.inf, -np.inf], 0), 2),\n",
    "    cost_per_order=lambda x: round((x['mark_spent'] / x['orders']).replace([np.inf, -np.inf], 0), 2),\n",
    "    roas=lambda x: round((x['revenue'] / x['mark_spent']).replace([np.inf, -np.inf], 0), 2),\n",
    "    ctr=lambda x: round((x['clicks'] / x['impressions'] * 100).replace([np.inf, -np.inf], 0), 2),\n",
    "    roi=lambda x: round(((x['revenue'] - x['mark_spent']) / x['mark_spent'] * 100).replace([np.inf, -np.inf], 0), 2)\n",
    ")\n",
    "\n",
    "print(\"\\nMetrics by Category:\")\n",
    "print(category_metrics[['conversion_rate', 'cost_per_click', 'cost_per_lead', \n",
    "                       'cost_per_order', 'roas', 'ctr', 'roi']])\n",
    "\n",
    "# Calculate metrics by platform\n",
    "platform_metrics = df.groupby('platform_name').agg({\n",
    "    'orders': 'sum',\n",
    "    'clicks': 'sum',\n",
    "    'mark_spent': 'sum',\n",
    "    'leads': 'sum',\n",
    "    'revenue': 'sum',\n",
    "    'impressions': 'sum'\n",
    "}).assign(\n",
    "    conversion_rate=lambda x: round((x['orders'] / x['clicks'] * 100).replace([np.inf, -np.inf], 0), 2),\n",
    "    cost_per_click=lambda x: round((x['mark_spent'] / x['clicks']).replace([np.inf, -np.inf], 0), 2),\n",
    "    cost_per_lead=lambda x: round((x['mark_spent'] / x['leads']).replace([np.inf, -np.inf], 0), 2),\n",
    "    cost_per_order=lambda x: round((x['mark_spent'] / x['orders']).replace([np.inf, -np.inf], 0), 2),\n",
    "    roas=lambda x: round((x['revenue'] / x['mark_spent']).replace([np.inf, -np.inf], 0), 2),\n",
    "    ctr=lambda x: round((x['clicks'] / x['impressions'] * 100).replace([np.inf, -np.inf], 0), 2),\n",
    "    roi=lambda x: round(((x['revenue'] - x['mark_spent']) / x['mark_spent'] * 100).replace([np.inf, -np.inf], 0), 2)\n",
    ")\n",
    "\n",
    "print(\"\\nMetrics by Platform:\")\n",
    "print(platform_metrics[['conversion_rate', 'cost_per_click', 'cost_per_lead',\n",
    "                       'cost_per_order', 'roas', 'ctr', 'roi']])\n",
    "\n",
    "# Calculate metrics by campaign\n",
    "campaign_metrics = df.groupby('campaign_name').agg({\n",
    "    'orders': 'sum', \n",
    "    'clicks': 'sum',\n",
    "    'mark_spent': 'sum',\n",
    "    'leads': 'sum',\n",
    "    'revenue': 'sum',\n",
    "    'impressions': 'sum'\n",
    "}).assign(\n",
    "    conversion_rate=lambda x: round((x['orders'] / x['clicks'] * 100).replace([np.inf, -np.inf], 0), 2),\n",
    "    cost_per_click=lambda x: round((x['mark_spent'] / x['clicks']).replace([np.inf, -np.inf], 0), 2),\n",
    "    cost_per_lead=lambda x: round((x['mark_spent'] / x['leads']).replace([np.inf, -np.inf], 0), 2),\n",
    "    cost_per_order=lambda x: round((x['mark_spent'] / x['orders']).replace([np.inf, -np.inf], 0), 2),\n",
    "    roas=lambda x: round((x['revenue'] / x['mark_spent']).replace([np.inf, -np.inf], 0), 2),\n",
    "    ctr=lambda x: round((x['clicks'] / x['impressions'] * 100).replace([np.inf, -np.inf], 0), 2),\n",
    "    roi=lambda x: round(((x['revenue'] - x['mark_spent']) / x['mark_spent'] * 100).replace([np.inf, -np.inf], 0), 2)\n",
    ")\n",
    "\n",
    "print(\"\\nMetrics by Campaign:\")\n",
    "print(campaign_metrics[['conversion_rate', 'cost_per_click', 'cost_per_lead',\n",
    "                       'cost_per_order', 'roas', 'ctr', 'roi']])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Average marketing metrics:\n",
      "conversion_rate       0.279176\n",
      "cost_per_click       11.270120\n",
      "cost_per_lead       540.941018\n",
      "cost_per_order     3960.360669\n",
      "roas                  1.407612\n",
      "dtype: float64\n"
     ]
    }
   ],
   "source": [
    "# Average Marketing Metric\n",
    "print(\"Average marketing metrics:\")\n",
    "metrics = ['conversion_rate', 'cost_per_click', 'cost_per_lead', 'cost_per_order', 'roas']\n",
    "print(df[metrics].mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Platform-wise performance metrics:\n",
      "               impressions   clicks  leads  orders     revenue   mark_spent\n",
      "platform_name                                                              \n",
      "banner          1068337427   420003  10149    1566   6152960.0   5026674.76\n",
      "facebook         146342429   719936  14378    1564   6696870.0  10167069.91\n",
      "google            72298252   330054   7107     828   3705065.0   3460400.07\n",
      "instagram        247055978  1079912  24014    2171  11023038.0   7878798.57\n",
      "youtube           43688313   450014   9931    1914  15311433.0   4057936.51\n"
     ]
    }
   ],
   "source": [
    "# Platform performance comparison\n",
    "print(\"Platform-wise performance metrics:\")\n",
    "platform_metrics = df.groupby('platform_name')[['impressions', 'clicks', 'leads', 'orders', 'revenue', 'mark_spent']].sum()\n",
    "print(platform_metrics)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Average Platform-wise performance metrics:\n",
      "                impressions        clicks       leads     orders  \\\n",
      "platform_name                                                      \n",
      "banner         3.815491e+07  15000.107143  362.464286  55.928571   \n",
      "facebook       1.306629e+06   6428.000000  128.375000  13.964286   \n",
      "google         1.291040e+06   5893.821429  126.910714  14.785714   \n",
      "instagram      2.941143e+06  12856.095238  285.880952  25.845238   \n",
      "youtube        1.560297e+06  16071.928571  354.678571  68.357143   \n",
      "\n",
      "                     revenue     mark_spent  \n",
      "platform_name                                \n",
      "banner         219748.571429  179524.098571  \n",
      "facebook        59793.482143   90777.409911  \n",
      "google          66161.875000   61792.858393  \n",
      "instagram      131226.642857   93795.221071  \n",
      "youtube        546836.892857  144926.303929  \n"
     ]
    }
   ],
   "source": [
    "print(\"Average Platform-wise performance metrics:\")\n",
    "platform_metrics_avg = df.groupby('platform_name')[['impressions', 'clicks', 'leads', 'orders', 'revenue', 'mark_spent']].mean()\n",
    "print(platform_metrics_avg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Category-wise performance:\n",
      "               revenue   mark_spent  orders\n",
      "category                                   \n",
      "influencer  21119887.0   8305304.08    3014\n",
      "media        6152960.0   5026674.76    1566\n",
      "search       3705065.0   3460400.07     828\n",
      "social      11911454.0  13798500.91    2635\n"
     ]
    }
   ],
   "source": [
    "# Category-wise analysis\n",
    "print(\"Category-wise performance:\")\n",
    "print(df.groupby('category')[['revenue', 'mark_spent', 'orders']].sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Category-wise average performance:\n",
      "                  revenue     mark_spent     orders\n",
      "category                                           \n",
      "influencer  377140.839286  148309.001429  53.821429\n",
      "media       219748.571429  179524.098571  55.928571\n",
      "search       66161.875000   61792.858393  14.785714\n",
      "social       70901.511905   82133.933988  15.684524\n"
     ]
    }
   ],
   "source": [
    "print(\"Category-wise average performance:\")\n",
    "print(df.groupby('category')[['revenue', 'mark_spent', 'orders']].mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Daily performance metrics:\n",
      "            impressions  clicks    revenue\n",
      "c_date                                    \n",
      "2021-02-01     22803592   14991   182676.0\n",
      "2021-02-02     46221902   29977   316369.0\n",
      "2021-02-03    140541998   89972  1583142.0\n",
      "2021-02-04     58853025  119976  1633899.0\n",
      "2021-02-05     42506259  150009  2704723.0\n",
      "2021-02-06     46269355   29996   416199.0\n",
      "2021-02-07      9269431   29987   434647.0\n",
      "2021-02-08      6385079   29952   308655.0\n",
      "2021-02-09      8113096   29977   437923.0\n",
      "2021-02-10     16137532   59994   811363.0\n",
      "2021-02-11     19919970   89988  1563209.0\n",
      "2021-02-12     30842580  120019  1657464.0\n",
      "2021-02-13     37516676  150017  2802893.0\n",
      "2021-02-14     34954508  179977  2197128.0\n",
      "2021-02-15     35881351  210012  2795172.0\n",
      "2021-02-16    100483322  239979  3620594.0\n",
      "2021-02-17     78040007  270005  4139131.0\n",
      "2021-02-18    472888319  299995  2659560.0\n",
      "2021-02-19     76466877  330047  4973240.0\n",
      "2021-02-20     82185688  359986  5261521.0\n",
      "2021-02-21     46112718   30018   436892.0\n",
      "2021-02-22    117990842   60012   893154.0\n",
      "2021-02-23      3847007   15008   162649.0\n",
      "2021-02-24      6398522   30013   440159.0\n",
      "2021-02-25      3572319    5996    99303.0\n",
      "2021-02-26     22930971   14991   238748.0\n",
      "2021-02-27       712672    2976    42789.0\n",
      "2021-02-28      9876781    6049    76164.0\n"
     ]
    }
   ],
   "source": [
    "# Daily trends\n",
    "print(\"Daily performance metrics:\")\n",
    "daily_metrics = df.groupby('c_date')[['impressions', 'clicks', 'revenue']].sum()\n",
    "print(daily_metrics)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Average CTR by platform:\n",
      "platform_name\n",
      "banner       0.129007\n",
      "facebook     1.261973\n",
      "google       1.229689\n",
      "instagram    0.642654\n",
      "youtube      0.987335\n",
      "Name: ctr, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "# Click-through rate (CTR) by platform\n",
    "df['ctr'] = (df['clicks'] / df['impressions']) * 100\n",
    "print(\"Average CTR by platform:\")\n",
    "print(df.groupby('platform_name')['ctr'].mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Top 5 campaigns by revenue:\n",
      "       campaign_name     c_date    revenue  mark_spent\n",
      "202  youtube_blogger 2021-02-19  2812520.0    880357.0\n",
      "213  youtube_blogger 2021-02-20  1452540.0    303860.0\n",
      "136  youtube_blogger 2021-02-13  1296550.0    205045.0\n",
      "180  youtube_blogger 2021-02-17  1189170.0    363811.0\n",
      "48   youtube_blogger 2021-02-05  1165230.0    442617.0\n"
     ]
    }
   ],
   "source": [
    "# Top performing campaigns\n",
    "print(\"Top 5 campaigns by revenue:\")\n",
    "print(df.nlargest(5, 'revenue')[['campaign_name','c_date', 'revenue', 'mark_spent']])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "10. Campaigns sorted by ROAS:\n",
      "            campaign_name      roas    revenue  mark_spent\n",
      "103       youtube_blogger  6.593987   130768.0    19831.40\n",
      "136       youtube_blogger  6.323246  1296550.0   205045.00\n",
      "40   facebook_retargeting  5.968953     5287.0      885.75\n",
      "224       youtube_blogger  5.182299   159620.0    30801.00\n",
      "158       youtube_blogger  5.163309   782138.0   151480.00\n"
     ]
    }
   ],
   "source": [
    "# Campaign efficiency\n",
    "print(\"Campaigns sorted by ROAS:\")\n",
    "print(df.sort_values('roas', ascending=False)[['campaign_name', 'roas', 'revenue', 'mark_spent']].head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Lead to order conversion rate by category:\n",
      "category\n",
      "influencer    17.525802\n",
      "media         14.603305\n",
      "search        11.482287\n",
      "social        11.835540\n",
      "Name: lead_to_order_rate, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "# Lead to order conversion rate by category\n",
    "df['lead_to_order_rate'] = (df['orders'] / df['leads']) * 100\n",
    "print(\"Lead to order conversion rate by category:\")\n",
    "print(df.groupby('category')['lead_to_order_rate'].mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of campaigns per platform:\n",
      "platform_name\n",
      "banner        28\n",
      "facebook     112\n",
      "google        56\n",
      "instagram     84\n",
      "youtube       28\n",
      "Name: campaign_name, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# Campaign count by platform\n",
    "print(\"Number of campaigns per platform:\")\n",
    "print(df.groupby('platform_name')['campaign_name'].count())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Average ROI by category:\n",
      "category\n",
      "influencer    170.003927\n",
      "media          24.226054\n",
      "search         19.141372\n",
      "social          7.642676\n",
      "Name: roi, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "#ROI analysis by category\n",
    "df['roi'] = ((df['revenue'] - df['mark_spent']) / df['mark_spent']) * 100\n",
    "print(\"Average ROI by category:\")\n",
    "print(df.groupby('category')['roi'].mean())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Average ROI by platform:\n",
      "platform_name\n",
      "banner        24.226054\n",
      "facebook       5.303523\n",
      "google        19.141372\n",
      "instagram     19.259925\n",
      "youtube      306.870044\n",
      "Name: roi, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "# ROI analysis by platform\n",
    "df['roi'] = ((df['revenue'] - df['mark_spent']) / df['mark_spent']) * 100\n",
    "print('Average ROI by platform:')\n",
    "print(df.groupby('platform_name')['roi'].mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Average ROI by campaign:\n",
      "campaign_name\n",
      "banner_partner           24.226054\n",
      "facebook_lal            -88.089815\n",
      "facebook_retargeting    145.026263\n",
      "facebook_tier1           -6.637590\n",
      "facebook_tier2          -29.084767\n",
      "google_hot               70.865333\n",
      "google_wide             -32.582589\n",
      "instagram_blogger        33.137810\n",
      "instagram_tier1          64.592078\n",
      "instagram_tier2         -39.950113\n",
      "youtube_blogger         306.870044\n",
      "Name: roi, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "# ROI analysis by campaign\n",
    "df['roi'] = ((df['revenue'] - df['mark_spent']) / df['mark_spent']) * 100\n",
    "print('Average ROI by campaign:')\n",
    "print(df.groupby('campaign_name')['roi'].mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Average CPC by category:\n",
      "category\n",
      "influencer    11.619038\n",
      "media         10.451950\n",
      "search        10.878875\n",
      "social        11.420590\n",
      "Name: cpc, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "#Cost per click (CPC) analysis\n",
    "df['cpc'] = df['mark_spent'] / df['clicks']\n",
    "print(\"Average CPC by category:\")\n",
    "print(df.groupby('category')['cpc'].mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Average CPC by platform:\n",
      "platform_name\n",
      "banner       10.451950\n",
      "facebook     14.690767\n",
      "google       10.878875\n",
      "instagram     7.774153\n",
      "youtube       9.676093\n",
      "Name: cpc, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "#Cost per click (CPC) analysis\n",
    "df['cpc'] = df['mark_spent'] / df['clicks']\n",
    "print(\"Average CPC by platform:\")\n",
    "print(df.groupby('platform_name')['cpc'].mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Average CPC by campaign:\n",
      "campaign_name\n",
      "banner_partner          10.451950\n",
      "facebook_lal            22.882911\n",
      "facebook_retargeting     9.011788\n",
      "facebook_tier1          13.657477\n",
      "facebook_tier2          13.210893\n",
      "google_hot              13.193599\n",
      "google_wide              8.564151\n",
      "instagram_blogger       13.561984\n",
      "instagram_tier1          7.882315\n",
      "instagram_tier2          1.878159\n",
      "youtube_blogger          9.676093\n",
      "Name: cpc, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "#Cost per click (CPC) analysis\n",
    "df['cpc'] = df['mark_spent'] / df['clicks']\n",
    "print(\"Average CPC by campaign:\")\n",
    "print(df.groupby('campaign_name')['cpc'].mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPL comparison across categories:\n",
      "category\n",
      "influencer    501.980490\n",
      "media         456.060290\n",
      "search        534.135126\n",
      "social        570.343279\n",
      "Name: cpl, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "# Cost per lead (CPL) analysis\n",
    "df['cpl'] = (df['mark_spent'] / df['leads']).replace([np.inf, -np.inf], 0)\n",
    "print(\"CPL comparison across categories:\") \n",
    "print(df.groupby('category')['cpl'].mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPL comparison across campaigns:\n",
      "campaign_name\n",
      "banner_partner           456.060290\n",
      "facebook_lal            1325.114512\n",
      "facebook_retargeting     408.982956\n",
      "facebook_tier1           645.935710\n",
      "facebook_tier2           552.082404\n",
      "google_hot               668.505406\n",
      "google_wide              399.764846\n",
      "instagram_blogger        588.802165\n",
      "instagram_tier1          396.585072\n",
      "instagram_tier2           93.359015\n",
      "youtube_blogger          415.158816\n",
      "Name: cpl, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "# Cost per lead (CPL) analysis\n",
    "df['cpl'] = df['mark_spent'] / df['leads']  \n",
    "df['cpl'] = df['cpl'].replace([np.inf, -np.inf], 0)  # Handle infinities after calculation\n",
    "print(\"CPL comparison across campaigns:\")\n",
    "print(df.groupby('campaign_name')['cpl'].mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPL comparison across platforms:\n",
      "platform_name\n",
      "banner       456.060290\n",
      "facebook     733.028896\n",
      "google       534.135126\n",
      "instagram    359.582084\n",
      "youtube      415.158816\n",
      "Name: cpl, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "# Cost per lead (CPL) analysis\n",
    "df['cpl'] = df['mark_spent'] / df['leads']  \n",
    "df['cpl'] = df['cpl'].replace([np.inf, -np.inf], 0)  # Handle infinities after calculation\n",
    "print(\"CPL comparison across platforms:\")\n",
    "print(df.groupby('platform_name')['cpl'].mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Revenue per impression by category:\n",
      "category\n",
      "influencer    0.276901\n",
      "media         0.020043\n",
      "search        0.233021\n",
      "social        0.142015\n",
      "Name: revenue_per_impression, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "#Revenue per impression\n",
    "df['revenue_per_impression'] = df['revenue'] / df['impressions']\n",
    "print(\"Revenue per impression by category:\")\n",
    "print(df.groupby('category')['revenue_per_impression'].mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Average order value by platform:\n",
      "platform_name\n",
      "banner       3888.777484\n",
      "facebook     4003.660317\n",
      "google       5359.647059\n",
      "instagram    4402.384615\n",
      "youtube      8046.609172\n",
      "Name: avg_order_value, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "#Average order value\n",
    "df['avg_order_value'] = df['revenue'] / df['orders']\n",
    "print(\"Average order value by platform:\")\n",
    "print(df.groupby('platform_name')['avg_order_value'].mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "21. Lead conversion rate by category:\n",
      "category\n",
      "influencer    2.309248\n",
      "media         2.215001\n",
      "search        2.043556\n",
      "social        2.017304\n",
      "Name: lead_conversion_rate, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "#Lead conversion rate\n",
    "df['lead_conversion_rate'] = (df['leads'] / df['clicks']) * 100\n",
    "print(\"Lead conversion rate by category:\")\n",
    "print(df.groupby('category')['lead_conversion_rate'].mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "22. Revenue contribution percentage by platform:\n",
      "platform_name\n",
      "banner       14.346120\n",
      "facebook     15.614290\n",
      "google        8.638656\n",
      "instagram    25.701098\n",
      "youtube      35.699835\n",
      "Name: revenue, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "#Revenue contribution analysis\n",
    "print(\"Revenue contribution percentage by platform:\")\n",
    "platform_revenue = df.groupby('platform_name')['revenue'].sum()\n",
    "print((platform_revenue / df['revenue'].sum()) * 100)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "23. Marketing efficiency ratio by category:\n",
      "category\n",
      "influencer    2.700039\n",
      "media         1.242261\n",
      "search        1.191414\n",
      "social        1.076427\n",
      "Name: mer, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "#Marketing efficiency ratio\n",
    "df['mer'] = df['revenue'] / df['mark_spent']\n",
    "print(\"Marketing efficiency ratio by category:\")\n",
    "print(df.groupby('category')['mer'].mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Marketing spend percentage by platform:\n",
      "platform_name\n",
      "banner       16.431939\n",
      "facebook     33.235624\n",
      "google       11.311868\n",
      "instagram    25.755384\n",
      "youtube      13.265184\n",
      "Name: mark_spent, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "# Cost structure analysis\n",
    "print(\"Marketing spend percentage by platform:\")\n",
    "platform_spend = df.groupby('platform_name')['mark_spent'].sum()\n",
    "print((platform_spend / df['mark_spent'].sum()) * 100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "25. Orders per 1000 impressions by platform:\n",
      "platform_name\n",
      "banner       0.005154\n",
      "facebook     0.044458\n",
      "google       0.032725\n",
      "instagram    0.016270\n",
      "youtube      0.046269\n",
      "Name: orders_per_impression, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "# Orders per impression\n",
    "df['orders_per_impression'] = (df['orders'] / df['impressions']) * 1000  # per 1000 impressions\n",
    "print(\"\\n25. Orders per 1000 impressions by platform:\")\n",
    "print(df.groupby('platform_name')['orders_per_impression'].mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "27. Average cost per order by platform:\n",
      "platform_name\n",
      "banner               inf\n",
      "facebook             inf\n",
      "google               inf\n",
      "instagram            inf\n",
      "youtube      2090.787332\n",
      "Name: cpo, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "#Cost per order (CPO)\n",
    "df['cpo'] = df['mark_spent'] / df['orders']\n",
    "print(\"Average cost per order by platform:\")\n",
    "print(df.groupby('platform_name')['cpo'].mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "28. Impression to lead conversion rate by category:\n",
      "category\n",
      "influencer    0.023237\n",
      "media         0.003373\n",
      "search        0.024639\n",
      "social        0.019211\n",
      "Name: impression_to_lead, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "#Impression to lead conversion\n",
    "df['impression_to_lead'] = (df['leads'] / df['impressions']) * 100\n",
    "print(\"\\n28. Impression to lead conversion rate by category:\")\n",
    "print(df.groupby('category')['impression_to_lead'].mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "29. Average revenue per click by platform:\n",
      "platform_name\n",
      "banner       12.893568\n",
      "facebook     11.540090\n",
      "google       14.300331\n",
      "instagram    11.194747\n",
      "youtube      36.802128\n",
      "Name: revenue_per_click, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "#Revenue per click\n",
    "df['revenue_per_click'] = df['revenue'] / df['clicks']\n",
    "print(\"\\n29. Average revenue per click by platform:\")\n",
    "print(df.groupby('platform_name')['revenue_per_click'].mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "30. Top campaigns by efficiency score:\n",
      "            campaign_name  efficiency_score\n",
      "282  facebook_retargeting          9.529246\n",
      "249  facebook_retargeting          9.386392\n",
      "271  facebook_retargeting          9.371584\n",
      "7    facebook_retargeting          9.146240\n",
      "227  facebook_retargeting          8.443414\n"
     ]
    }
   ],
   "source": [
    "# Campaign efficiency score\n",
    "# Combining multiple metrics into a single score\n",
    "df['efficiency_score'] = (df['roas'] * 0.4) + (df['ctr'] * 0.2) + (df['lead_conversion_rate'] * 0.2) + (df['lead_to_order_rate'] * 0.2)\n",
    "print(\"\\n30. Top campaigns by efficiency score:\")\n",
    "print(df.sort_values('efficiency_score', ascending=False)[['campaign_name', 'efficiency_score']].head())\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. Performance by Category: \n",
    "\n",
    "Influencer campaigns are the most effective overall:\n",
    "* Highest conversion rate (40%) and ROI (154.29).\n",
    "* Best cost efficiency with the lowest cost per lead ($490.31) and cost per order ($2755.58).\n",
    "* Highest ROAS (2.54), indicating strong returns for every dollar spent.\n",
    "\n",
    "Social campaigns are underperforming:\n",
    "* Lowest conversion rate (18%) and negative ROI (-13.68).\n",
    "* High cost per lead ($439.67) and cost per order ($5236.62), making them inefficient.\n",
    "\n",
    "Recommendation:\n",
    "* Focus on scaling influencer campaigns while revisiting or optimizing social campaigns to improve their performance.\n",
    "\n",
    "2. Performance by Platform\n",
    "YouTube is the top-performing platform:\n",
    "* Highest conversion rate (43%), ROAS (3.77), and ROI (277.32).\n",
    "* Low cost per order ($2120.13) and cost per lead ($408.61), making it highly efficient.\n",
    "\n",
    "Facebook is struggling:\n",
    "* Negative ROI (-34.13) despite a decent CTR (0.49).\n",
    "* High cost per order ($6500.68) and cost per lead ($707.13), making it the least efficient platform.\n",
    "\n",
    "Instagram shows potential:\n",
    "* Low cost per click ($7.30) and moderate ROI (39.91), but its conversion rate (20%) could be improved.\n",
    "\n",
    "Recommendation:\n",
    "* Invest more in YouTube campaigns, particularly YouTube Blogger campaigns, as they deliver exceptional returns. Reevaluate Facebook campaigns to identify inefficiencies, and consider optimizing Instagram campaigns to increase conversions.\n",
    "\n",
    "3. Performance by Campaign\n",
    "Top-performing campaigns:\n",
    "- YouTube Blogger campaign leads with:\n",
    "* Highest conversion rate (43%), ROAS (3.77), and ROI (277.32).\n",
    "* Lowest cost per order ($2120.13).\n",
    "\n",
    "Facebook Retargeting campaign performs well with:\n",
    "* High conversion rate (36%) and ROI (101.50).\n",
    "* Cost efficiency compared to other Facebook campaigns.\n",
    "\n",
    "Underperforming campaigns:\n",
    "Facebook LAL campaign has:\n",
    "* Negative ROI (-88.64) and high costs, making it highly inefficient.\n",
    "* High cost per lead ($1383.94) and cost per order ($8986.19).\n",
    "\n",
    "Instagram Tier 2 campaign has:\n",
    "* Lowest conversion rate (6%) and negative ROI (-37.11).\n",
    "\n",
    "Recommendation:\n",
    "* Prioritize YouTube Blogger and Facebook Retargeting campaigns for scaling efforts. Discontinue or significantly optimize Facebook LAL and Instagram Tier 2 campaigns to reduce wasted spend.\n",
    "\n",
    "4. General Observations\n",
    "* Influencer marketing consistently outperforms other categories in terms of ROI, ROAS, and conversion rates.\n",
    "* Platforms like YouTube and Instagram show strong potential for growth due to their combination of high engagement (CTR) and relatively low costs.\n",
    "* Social campaigns, particularly on Facebook, require closer attention as they are currently delivering poor returns.\n",
    "\n",
    "Final Recommendations\n",
    "\n",
    "Scale Investments in Influencer Marketing:\n",
    "* Focus on influencer-driven campaigns as they deliver the best combination of high conversions, low costs, and strong returns.\n",
    "\n",
    "Increase Budget for YouTube Campaigns:\n",
    "* Specifically invest in YouTube Blogger campaigns as they outperform all others across key metrics.\n",
    "\n",
    "Optimize or Discontinue Underperforming Campaigns:\n",
    "* Reevaluate Facebook LAL and Instagram Tier 2 campaigns to either improve their efficiency or reallocate budgets to better-performing channels.\n",
    "\n",
    "Test New Strategies for Social Campaigns:\n",
    "* Experiment with creative content or audience targeting for social platforms like Facebook to improve their ROI.\n",
    "\n",
    "Monitor Cost Efficiency Across Platforms:\n",
    "* Keep an eye on rising costs for platforms like Facebook to ensure they remain within acceptable thresholds relative to performance."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
